from flask import Blueprint, jsonify, request
from models.sync_config import DatabaseConnection, SyncTask, SyncTaskRun, db
from services.database_service import DatabaseService
from services.sync_service import SyncService
import json

sync_bp = Blueprint('sync', __name__)

@sync_bp.route('/connections', methods=['GET'])
def list_connections():
    """获取所有数据库连接"""
    try:
        connections = DatabaseConnection.query.all()
        return jsonify([conn.to_dict() for conn in connections])
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@sync_bp.route('/connections', methods=['POST'])
def create_connection():
    """创建新的数据库连接"""
    try:
        data = request.json
        
        # 必要参数检查
        required_fields = ['name', 'db_type', 'host', 'port', 'username', 'password', 'database']
        for field in required_fields:
            if field not in data:
                return jsonify({"error": f"缺少必要参数: {field}"}), 400
        
        # 检查数据库类型
        if data['db_type'] not in ['mysql', 'postgresql']:
            return jsonify({"error": "不支持的数据库类型，支持 mysql 或 postgresql"}), 400
        
        # 测试连接
        success, message = DatabaseService.test_connection(
            data['db_type'],
            data['host'],
            int(data['port']),
            data['username'],
            data['password'],
            data['database']
        )
        
        if not success:
            return jsonify({"error": f"连接测试失败: {message}"}), 400
        
        # 创建连接记录
        connection = DatabaseConnection(
            name=data['name'],
            db_type=data['db_type'],
            host=data['host'],
            port=int(data['port']),
            username=data['username'],
            password=data['password'],
            database=data['database']
        )
        
        db.session.add(connection)
        db.session.commit()
        
        return jsonify({
            "id": connection.id,
            "message": "数据库连接创建成功"
        })
    except Exception as e:
        db.session.rollback()
        return jsonify({"error": str(e)}), 500

@sync_bp.route('/connections/<int:conn_id>', methods=['GET'])
def get_connection(conn_id):
    """获取单个数据库连接信息"""
    try:
        connection = DatabaseConnection.query.get_or_404(conn_id)
        return jsonify(connection.to_dict())
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@sync_bp.route('/connections/<int:conn_id>', methods=['DELETE'])
def delete_connection(conn_id):
    """删除数据库连接"""
    try:
        connection = DatabaseConnection.query.get_or_404(conn_id)
        
        # 检查是否有相关的同步任务
        tasks = SyncTask.query.filter_by(database_connection_id=conn_id).all()
        if tasks:
            return jsonify({"error": "该连接下存在同步任务，请先删除相关同步任务"}), 400
        
        db.session.delete(connection)
        db.session.commit()
        
        return jsonify({"message": "数据库连接已删除"})
    except Exception as e:
        db.session.rollback()
        return jsonify({"error": str(e)}), 500

@sync_bp.route('/connections/test', methods=['POST'])
def test_connection():
    """测试数据库连接"""
    try:
        data = request.json
        
        # 必要参数检查
        required_fields = ['db_type', 'host', 'port', 'username', 'password', 'database']
        for field in required_fields:
            if field not in data:
                return jsonify({"error": f"缺少必要参数: {field}"}), 400
        
        # 检查数据库类型
        if data['db_type'] not in ['mysql', 'postgresql']:
            return jsonify({"error": "不支持的数据库类型，支持 mysql 或 postgresql"}), 400
        
        # 测试连接
        success, message = DatabaseService.test_connection(
            data['db_type'],
            data['host'],
            int(data['port']),
            data['username'],
            data['password'],
            data['database']
        )
        
        if success:
            return jsonify({"message": message})
        else:
            return jsonify({"error": message}), 400
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@sync_bp.route('/preview', methods=['POST'])
def preview_query():
    """预览SQL查询结果"""
    try:
        data = request.json
        
        # 必要参数检查
        if 'connection_id' not in data:
            return jsonify({"error": "缺少必要参数: connection_id"}), 400
        if 'sql_query' not in data:
            return jsonify({"error": "缺少必要参数: sql_query"}), 400
        
        # 获取连接信息
        connection = DatabaseConnection.query.get_or_404(data['connection_id']).to_dict()
        
        # 执行查询预览
        success, result = DatabaseService.execute_query(
            connection,
            data['sql_query'],
            limit=100  # 限制返回100条记录
        )
        
        if success:
            return jsonify(result)
        else:
            return jsonify({"error": result}), 400
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@sync_bp.route('/tasks', methods=['GET'])
def list_tasks():
    """获取所有同步任务"""
    try:
        tasks = SyncTask.query.all()
        result = []
        
        for task in tasks:
            task_dict = task.to_dict()
            
            # 获取数据库连接信息
            connection = DatabaseConnection.query.get(task.database_connection_id)
            if connection:
                task_dict['connection_name'] = connection.name
                task_dict['db_type'] = connection.db_type
            
            # 获取最近一次运行状态
            last_run = SyncTaskRun.query.filter_by(sync_task_id=task.id).order_by(SyncTaskRun.start_time.desc()).first()
            if last_run:
                task_dict['last_run'] = last_run.to_dict()
            
            result.append(task_dict)
        
        return jsonify(result)
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@sync_bp.route('/tasks', methods=['POST'])
def create_task():
    """创建新的同步任务"""
    try:
        data = request.json
        
        # 必要参数检查
        required_fields = ['name', 'database_connection_id', 'sql_query', 'knowledge_id']
        for field in required_fields:
            if field not in data:
                return jsonify({"error": f"缺少必要参数: {field}"}), 400
        
        # 检查数据库连接是否存在
        connection = DatabaseConnection.query.get(data['database_connection_id'])
        if not connection:
            return jsonify({"error": "指定的数据库连接不存在"}), 400
        
        # 确保字段列表为JSON字符串
        document_fields = json.dumps(data.get('document_fields', []))
        metadata_fields = json.dumps(data.get('metadata_fields', []))
        embedding_fields = json.dumps(data.get('embedding_fields', []))
        
        # 创建同步任务
        task = SyncTask(
            name=data['name'],
            database_connection_id=data['database_connection_id'],
            sql_query=data['sql_query'],
            knowledge_id=data['knowledge_id'],
            document_fields=document_fields,
            metadata_fields=metadata_fields,
            embedding_fields=embedding_fields,
            status='pending'
        )
        
        db.session.add(task)
        db.session.commit()
        
        return jsonify({
            "id": task.id,
            "message": "同步任务创建成功"
        })
    except Exception as e:
        db.session.rollback()
        return jsonify({"error": str(e)}), 500

@sync_bp.route('/tasks/<int:task_id>', methods=['GET'])
def get_task(task_id):
    """获取单个同步任务信息"""
    try:
        task = SyncTask.query.get_or_404(task_id)
        task_dict = task.to_dict()
        
        # 获取数据库连接信息
        connection = DatabaseConnection.query.get(task.database_connection_id)
        if connection:
            task_dict['connection_name'] = connection.name
            task_dict['db_type'] = connection.db_type
        
        # 获取最近一次运行状态
        last_run = SyncTaskRun.query.filter_by(sync_task_id=task.id).order_by(SyncTaskRun.start_time.desc()).first()
        if last_run:
            task_dict['last_run'] = last_run.to_dict()
        
        return jsonify(task_dict)
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@sync_bp.route('/tasks/<int:task_id>', methods=['DELETE'])
def delete_task(task_id):
    """删除同步任务"""
    try:
        task = SyncTask.query.get_or_404(task_id)
        
        # 检查任务是否在运行中
        if task.status == 'running':
            return jsonify({"error": "任务正在运行中，请先取消任务"}), 400
        
        # 删除相关的运行记录
        SyncTaskRun.query.filter_by(sync_task_id=task_id).delete()
        
        # 删除任务
        db.session.delete(task)
        db.session.commit()
        
        return jsonify({"message": "同步任务已删除"})
    except Exception as e:
        db.session.rollback()
        return jsonify({"error": str(e)}), 500

@sync_bp.route('/tasks/<int:task_id>/start', methods=['POST'])
def start_task(task_id):
    """启动同步任务"""
    try:
        task = SyncTask.query.get_or_404(task_id)
        
        # 启动任务
        success, result = SyncService.start_sync_task(task)
        
        if success:
            return jsonify({
                "message": "同步任务已启动",
                "task_run_id": result
            })
        else:
            return jsonify({"error": result}), 400
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@sync_bp.route('/tasks/<int:task_id>/cancel', methods=['POST'])
def cancel_task(task_id):
    """取消同步任务"""
    try:
        success, message = SyncService.cancel_task(task_id)
        
        if success:
            return jsonify({"message": message})
        else:
            return jsonify({"error": message}), 400
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@sync_bp.route('/runs/<int:run_id>', methods=['GET'])
def get_task_run(run_id):
    """获取任务运行状态"""
    try:
        status = SyncService.get_task_status(run_id)
        
        if status:
            return jsonify(status)
        else:
            return jsonify({"error": "指定的任务运行不存在"}), 404
    except Exception as e:
        return jsonify({"error": str(e)}), 500 